# coding=utf-8
# Author: hang.zhang
# Date: 2024-06-29 05:13:34
# LastEditors: hang.zhang
# LastEditTime: 2024-06-29 12:30:27

import pandas as pd  
from openpyxl import Workbook  
from openpyxl.utils.dataframe import dataframe_to_rows  
from openpyxl.styles import Alignment  
import pandas as pd  
from openpyxl import load_workbook  
from openpyxl.worksheet.dimensions import ColumnDimension  
  
# 加载写入的Excel文件
# wb = load_workbook('/Users/zyt/Desktop/work/me/志愿填报/带专业.xlsx', data_only=True)
# 1. 读取Excel文件  
# df = pd.read_excel()  
path = "/Users/zyt/Downloads/aa无标题.xlsx"
# 使用openpyxl加载Excel文件  

wb = load_workbook(path)  
ws = wb.active  
# 定义一个函数来合并具有相同标签的单元格
def merge_same_cells(ws, column_index, start_row=1):
    unique_values = set()
    should_merge = False
    merge_range = None

    for row in range(start_row, ws.max_row + 1):
        cell_value = ws.cell(row=row, column=column_index).value
        if cell_value in unique_values:
            # 如果当前值与之前某个值相同，则开始或扩展合并范围
            if not should_merge:
                merge_range = ws.cell(row=row-1, column=column_index), ws.cell(row=row, column=column_index)
                should_merge = True
            else:
                merge_range = (merge_range[0], ws.cell(row=row, column=column_index))
        else:
            # 如果遇到新值，结束之前的合并并开始检查下一个
            if should_merge:
                ws.merge_cells(start_row=merge_range[0].row, start_column=merge_range[0].column,
                               end_row=merge_range[1].row, end_column=merge_range[1].column)
                should_merge = False
            unique_values.add(cell_value)
            
    # 最后一个值也需要检查是否需要合并
    if should_merge:
        ws.merge_cells(start_row=merge_range[0].row, start_column=merge_range[0].column,
                       end_row=merge_range[1].row, end_column=merge_range[1].column)

# 定义一个函数来根据指定列（这里是"学校"列）合并单元格，并在此基础上正确处理"专业组"列的合并
# 定义一个函数来根据指定列（这里是"学校"列）合并单元格，并在此基础上正确处理"专业组"列的合并
def merge_correctly_by_school_and_major(ws, school_column=1, major_column=2):
    last_school = None
    last_major = None
    merge_start_row = 1

    for row in range(2, ws.max_row + 1):
        current_school = ws.cell(row=row, column=school_column).value
        if current_school == None:
            current_school = last_school
        current_major = ws.cell(row=row, column=major_column).value

        # 检查是否进入新的学校
        if current_school != last_school:
            # 如果之前有学校和专业组在进行合并，则结束上一个合并
            if last_school is not None:
                ws.merge_cells(start_row=merge_start_row, start_column=major_column, 
                               end_row=row-1, end_column=major_column)
            # 重置学校和专业组的上次值，准备新的学校内的合并
            last_school = current_school
            last_major = current_major
            merge_start_row = row
        # 在同一所学校内，检查专业组是否变化
        elif current_major != last_major:
            # 如果专业组变化，结束上一个专业组的合并范围
            print("专业组变化", row, major_column,  row-1, current_major, last_major, current_school, last_school)
            ws.merge_cells(start_row=merge_start_row, start_column=major_column, 
                           end_row=row-1, end_column=major_column)
            # 更新当前专业组为新的开始
            last_major = current_major
            merge_start_row = row

    # 确保处理完所有行后，最后一个专业组也被合并
    if last_school is not None and last_major is not None:
        ws.merge_cells(start_row=merge_start_row, start_column=major_column, 
                       end_row=ws.max_row, end_column=major_column)
# 应用修正后的合并逻辑到"专业组"列（假设是第3列），依据"学校"列（第2列）

# 合并"学校"列的单元格
merge_same_cells(ws, column_index=1)  # 假设"学校"列是第2列（在Excel中列索引从1开始）

# # 合并"专业组"列的单元格，注意调整column_index为实际的列索引
# merge_same_cells(ws, column_index=2)  # 假设"专业组"列是第3列

# 根据"学校"列（假设是第2列）来合并"专业组"列（假设是第3列）的单元格
merge_correctly_by_school_and_major(ws)

  
# 保存Excel文件
wb.save(path+"_export.xlsx")